package com.zhucai.credit.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.zhucai.credit.model.EntAccountEntity;

/**
 * 金融账号Repository
 */
@Repository
public interface EntAccountRepository extends JpaRepository<EntAccountEntity,Long> {

    /**
     * 根据主键查询记录
     * @param id 主键id
     * @return 金融账号集合
     */
    EntAccountEntity findByTid(Long id);

	/**
	 * 更新最新委托书的主键id
	 * @param long1 
	 */
	@Query(value="update finance_ent_account set certi_id=?1 where id=?2",nativeQuery=true)
	void updateEntAccCetriId(Long cetriId, Long tid);

	/**
	 * 根据组织编号查询金融信息
	 * @date 2017年11月28日 @user hsg
	 */
	EntAccountEntity findByStatusNotAndOrgNum(int delete, String appOrgnNum);


	/**
	 * 根据企业id和商家类型查询金融信息
	 * @param delete
	 * @param enId
	 * @param entType
	 * @return
	 */
	EntAccountEntity findByStatusNotAndEnterpriseIdAndEntType(int delete, Long enId, String entType);

	/**
	 * 根据超管id查询下面的资金总调度
	 * @param userId
	 * @param delete
	 * @return
	 */
	@Query(value="select txu.id user_id from tb_xa_user txu "
				 +"LEFT JOIN tb_xa_user_role  txur  on txur.user_id=txu.id "
				 +"left join tb_xa_role txr on txur.role_id=txr.id "
				 +"where 1=1 and txr.role_name ='采购商/资金总调度' "
				 +"and txu.status<>?2 "
				 +"and txu.company_user_id=?1",nativeQuery=true)
	Long findZDDId(Long userId, int delete);

	/**
	 * 根据组织机构代码和商家类型查询金融信息
	 * @param delete
	 * @param orgn_num
	 * @return
	 */
	EntAccountEntity findByStatusNotAndOrgNumAndEntType(int delete, String orgn_num,String entType);
	/**
	 * 查询核心企业的的超管userId
	 * @param delete
	 * @param orgn_num
	 * @return
	 */
	@Query(value="select p.user_id from finance_ent_account f"
			+ " left join tb_xa_purchaser p on p.id = f.enterprise_id "
			+ " where f.id = ?1 and f.ent_type ='PURCHASER'",nativeQuery=true)
	Long findPurUserId(Long tid);
	/**
	 * 查询供应商的的超管userId
	 * @param delete
	 * @param orgn_num
	 * @return
	 */
	@Query(value="select s.user_id from finance_ent_account f"
			+ " left join tb_xa_supplier s on s.id = f.enterprise_id "
			+ " where f.id = ?1 and f.ent_type ='SUPPLIER'",nativeQuery=true)
	Long findSupUserId(Long tid);

	@Query(value="select fea.* from finance_ent_account fea "
			+ " left join finance_ent_cr_prod fecp on fea.id = fecp.en_acc_id and fecp.ecp_status = 0 and fecp.en_cr_status = 'PASS'"
			+ "	left join finance_product_type fpt on fecp.cr_pr_type_id = fpt.id"
			+ " where fpt.cr_prod_type = ?1 and fecp.zhucai_prod_type = ?2 and fea.ent_type = ?3",nativeQuery = true)
	List<EntAccountEntity> searchPurEnt(String bankType, String zhuBaoTong,String entType);

}
